package com.wanczy.dao.impl;

import java.sql.Connection;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.wanczy.dao.UserRoleTableDAO;
import com.wanczy.pojo.UserRoleTable;

public class UserRoleTableDAOImpl implements UserRoleTableDAO {
	public Connection conn;
	public UserRoleTableDAOImpl(Connection conn) {
		this.conn = conn;
	} 

	@Override
	public List<UserRoleTable> find(int userId) {
		List<UserRoleTable> list = null;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "select t.user_account,a.role_name,a.role_id from user_table t,role_table a,user_role_table b " + 
					"where b.user_id = t.user_id and b.role_id = a.role_id and t.user_id = ?  order by user_account";
			Object params[] = {userId};
			list =  qRunner.query(conn, sql,new BeanListHandler<>(UserRoleTable.class),params);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean doIns(UserRoleTable userRole) {
		boolean flag = false;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "insert into user_role_table(user_role_id,role_id,user_id) values (DH18.nextval,?,?)";
			Object param[] = {userRole.getRole_id(),userRole.getUser_id()};
			int x = qRunner.update(conn, sql, param);
			if(x == 1) {
				flag =  true;
			}else {
				flag =  false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return flag;
	}

	@Override
	public boolean doDel(int userId) {
		boolean flag = false;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "delete from user_role_table where user_id = ?";
			Object param[] = {userId};
			int x = qRunner.update(conn, sql, param);
			if(x == 1) {
				flag =  true;
			}else {
				flag =  false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return flag;
	}

	@Override
	public List<UserRoleTable> findAllRole() {
		List<UserRoleTable> list = null;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "select a.role_id, a.role_name from role_table a";
			list =  qRunner.query(conn, sql,new BeanListHandler<>(UserRoleTable.class));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<UserRoleTable> findAllUSer() {
		List<UserRoleTable> list = null;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "select t.user_account,t.user_id from user_table t ";
			list =  qRunner.query(conn, sql,new BeanListHandler<>(UserRoleTable.class));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}



